﻿using System;
using System.IO;
using KPIS.GERP.GFMS.BLL;
using KPIS.GERP.GFMS.MODEL;
using OfficeOpenXml;
using System.Data;
using System.Linq;
using KPIS.GERP.GFMS.DAL;
using KPIS.GERP.GFMS.APP.App_UI.Commons;
using System.Collections;
using System.Collections.Generic;
using System.Web.UI;

namespace KPIS.GERP.GFMS.APP.App_UI.Forms
{
    public partial class FIM_PayrollImporterUpload : System.Web.UI.Page
    {
        Main main = new Main();
        private B_FIM_Payroll bll = new B_FIM_Payroll();
        private D_FIM_Payroll dal = new D_FIM_Payroll();
        D_HRM_PS_Employees emp = new D_HRM_PS_Employees();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                lblError.Text = Convert.ToString(Request["errormessage"]);
                ddlMonth.SelectedValue = DateTime.Today.Month.ToString();
                txbYearNo.Text = (DateTime.Today.Year + 543).ToString();
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string errorResult = null;
            int year;// = Convert.ToInt32(ddlMonth.SelectedValue);
            int month;// = Convert.ToInt32(txbYearNo.Value);

            if (!int.TryParse(txbYearNo.Text, out year))
            {
                errorResult = string.Format("[ไม่สำเร็จ] {0}", "ปีไม่ถูกต้อง");
            }

            if (!int.TryParse(ddlMonth.SelectedValue, out month))
            {
                errorResult = string.Format("[ไม่สำเร็จ] {0}", "เดือนไม่ถูกต้อง");
            }

            if (!FileUpload1.HasFile)
            {
                errorResult = string.Format("[ไม่สำเร็จ] {0}", "ไม่พบ file ที่ต้องการ");
            }

            DataTable dtb = GetDataTableFromExcel();
            IList<M_FIM_PayrollDtls> models = new List<M_FIM_PayrollDtls>();

            foreach (DataRow dr in dtb.Rows)
            {
                string citizenID = dr["Personal ID"].ToString().Replace("-", "").Replace(" ", "");
                int emp_seq = emp.GetInfo(citizenID).emp_seq;

                M_FIM_PayrollDtls model = new M_FIM_PayrollDtls
                {
                    year = year,
                    month = month,
                    emp_seq = emp_seq,
                    salary = main.ConvertNullDouble(dr["Salary"].ToString().Replace("-", "").Replace(" ", "")),
                    extra_add = main.ConvertNullDouble(dr["Add"].ToString().Replace("-", "").Replace(" ", "")),
                    prof = main.ConvertNullDouble(dr["Prof/Pos Allow"].ToString().Replace("-", "").Replace(" ", "")),
                    previous = main.ConvertNullDouble(dr["SpecialAdd"].ToString().Replace("-", "").Replace(" ", "")),
                    tax = main.ConvertNullDouble(dr["Tax"].ToString().Replace("-", "").Replace(" ", "")),
                    coop = main.ConvertNullDouble(dr["Coop"].ToString().Replace("-", "").Replace(" ", "")),
                    house_bank = main.ConvertNullDouble(dr["HouseBank"].ToString().Replace("-", "").Replace(" ", "")),
                    office_loan = main.ConvertNullDouble(dr["OfficeLoan"].ToString().Replace("-", "").Replace(" ", "")),
                    pf_staff = main.ConvertNullDouble(dr["PFStaff"].ToString().Replace("-", "").Replace(" ", "")),
                    other_deduction = main.ConvertNullDouble(dr["OtherDeduction"].ToString().Replace("-", "").Replace(" ", "")),
                    net = main.ConvertNullDouble(dr["Net"].ToString().Replace("-", "").Replace(" ", "")),
                    pf_office = main.ConvertNullDouble(dr["PFOffice"].ToString().Replace("-", "").Replace(" ", "")),
                    survey_add = main.ConvertNullDouble(dr["Survey  Addendum"].ToString().Replace("-", "").Replace(" ", "")),
                };

                models.Add(model);
            }

            try
            {
                dal.DeleteAllDetail(year, month);
                dal.InsertDetail(models);
                dal.UpdatePayroll(year, month, new SsMng().EmpSeqI);
                lblSuccess.Text = string.Format("[สำเร็จ] โปรแกรมได้นำเข้าค่าใช้จ่ายเงินเดือน {0} รายการ เข้าไปที่ค่าใช้จ่ายเงินเดือน {1} ปี {2}",
                                                    dtb.Rows.Count.ToString("#,##0"), ddlMonth.Text, txbYearNo.Text);
                FileUpload1.Enabled = false;
                Button1.Enabled = false;
            }
            catch (Exception ex)
            {
                errorResult = string.Format("[ไม่สำเร็จ] {0}", ex.Message);
            }

            if (!string.IsNullOrEmpty(errorResult))
            {
                // Resolved for reuploading, because of Control Upload Asp can't re-upload after 1 postback;
                Response.Redirect(string.Format("FIM_PayrollImporterUpload.aspx?month={0}&year={1}&errormessage={2}",
                                                Request["month"], Request["year"], errorResult));
            }
        }

        public DataTable GetDataTableFromExcel()
        {
            /******************** HardCode ************************/
            int FirstColumn = 1; //firstRowCell.Start.Column
            int LastColumn = 24; //ws.Dimension.End.Column
            /******************************************************/

            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = FileUpload1.PostedFile.InputStream)
                {
                    pck.Load(stream);
                }

                var ws = pck.Workbook.Worksheets.First();
                DataTable dtb = new DataTable();
                bool hasHeader = true;

                foreach (var firstRowCell in ws.Cells[1, 1, 1, LastColumn])
                {
                    dtb.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", FirstColumn));
                }

                var startRow = hasHeader ? 3 : 1;

                for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    if (ws.Cells[rowNum, 1].Text != "")
                    {
                        var wsRow = ws.Cells[rowNum, 1, rowNum, LastColumn];
                        var row = dtb.NewRow();

                        foreach (var cell in wsRow)
                        {
                            row[cell.Start.Column - 1] = cell.Text;
                        }
                        dtb.Rows.Add(row);
                    }
                }

                return dtb;
            }
        }

        /*

        private void ReadPayrollImportExcel(Stream file)
        {
            string errorResult = null;
            int amountPr = 0, amountDd = 0;
            int created_by;
            Int32.TryParse(Convert.ToString(Session["EmpSeq"]), out created_by);

            try
            {
                using (ExcelPackage xlPackage = new ExcelPackage(file))
                {
                    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
                    if (worksheet != null)
                    {
                        int yIndex = 2;
                        int payroll_seq = 0;
                        object v;
                        while ((v = worksheet.Cells["B" + (yIndex++)].Value) != null)
                        {
                            var title = v.ToString();
                            if (string.IsNullOrEmpty(title)) break;

                            int payrollNumber;
                            decimal amount;
                            string isPayrollOrDeduction;
                            string numberCellRef = "A" + (yIndex - 1);
                            string amountCellRef = "C" + (yIndex - 1);
                            object payrollNumberCell, payrollAmountCell;

                            payrollNumberCell = (worksheet.Cells[numberCellRef].Value ?? new object());
                            payrollAmountCell = (worksheet.Cells[amountCellRef].Value ?? new object());
                            isPayrollOrDeduction = Int32.TryParse(payrollNumberCell.ToString(), out payrollNumber) ? "Payroll" : "Deduction";
                            Decimal.TryParse(payrollAmountCell.ToString(), out amount);

                            if (isPayrollOrDeduction == "Payroll")
                            {
                                var pr = new M_FIM_Payroll();
                                pr.ref_no = new B_CodeGenerate().GetNextCode(TransectionType.FimPayrollRefNo, "");
                                pr.title = title;
                                pr.amount = amount;
                                pr.req_date = DateTime.Now;
                                pr.emp_type_seq = 1;
                                pr.record_status = "U";
                                pr.ConId = 1;
                                pr.month_no = Convert.ToInt32(ddlMonth.SelectedValue);
                                pr.year_no = Convert.ToInt32(txbYearNo.Value);
                                pr.bgm_year_no = DateTime.Now.Year + (DateTime.Now.Month >= 10 ? 1 : 0);
                                pr.created_by = created_by;
                                pr.created_when = DateTime.Now;

                                payroll_seq = bll.InsertPayroll(pr);
                                amountPr++;

                            }
                            else
                            {
                                if (payroll_seq == 0) throw new Exception("บรรทัดเงินหักต้องอยู่ภายใต้ค่าใช้จ่ายเงินเดือน");

                                var dd = new M_FIM_PayrollDeduction();
                                dd.title = title;
                                dd.amount = amount;
                                dd.payroll_seq = payroll_seq;
                                dd.ConId = 1;
                                dd.record_status = "U";
                                dd.created_by = created_by;
                                dd.created_when = DateTime.Now;

                                bll.InsertPayrollDeduction(dd);
                                amountDd++;
                            }
                        }
                        lblSuccess.Text = string.Format("[สำเร็จ] โปรแกรมได้นำเข้าค่าใช้จ่ายเงินเดือน {0} รายการ, และเงินหัก {1} รายการ เข้าไปที่ค่าใช้จ่ายเงินเดือน {2} ปี {3}",
                                                        amountPr, amountDd, ddlMonth.Text, txbYearNo.Text);
                        FileUpload1.Enabled = false;
                        Button1.Enabled = false;
                    }
                    else
                    {
                        errorResult = string.Format("[ไม่สำเร็จ] ไม่พบ{0} Worksheet ที่ 2, กรุณาใช้ Template ที่ถูกต้อง สามารถโหลดได้ที่ลิงค์ (ดาวน์โหลดต้นแบบ)", "ARG0");
                    }
                }
            }
            catch (Exception ex)
            {
                errorResult = string.Format("[ไม่สำเร็จ] {0}", ex.Message);
            }

            if (!string.IsNullOrEmpty(errorResult))
            {
                // Resolved for reuploading, because of Control Upload Asp can't re-upload after 1 postback;
                Response.Redirect(string.Format("FIM_PayrollImporterUpload.aspx?month={0}&year={1}&errormessage={2}",
                                                Request["month"], Request["year"], errorResult));
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ReadPayrollImportExcel(FileUpload1.PostedFile.InputStream);
        }
        */
    }
}